# Process RAW PUBLIC SIGNALS DATA 
rm(list=ls())

(WD <- getwd())
if (!is.null(WD)) setwd(WD)

library(haven)
library(AER)
library(sandwich)
library(lmtest)
library(pracma)
library(stargazer)
library(plm)
library(pracma)
library(DataCombine)
library(jtools)
library(plyr)
library(readxl)
library(foreign)

lagpad <- function(x, k) {
  res <- c(rep(NA, k), x)[1:length(x)]
  return(res)
}

##### NEER DATA #############
data    = read_excel("neer_bis.xlsx", sheet = "Nominal")
data         = data[-2:-1,]
data         = data[seq(1, nrow(data), 3), ]
data$neer    = as.numeric(data$NNXM) 
data$dlneer  = 100*(log(data$neer)-log(lagpad(data$neer,4)))
tmp          = row(data)
tmp          = tmp[,1]
data$qdate   = 1964+(tmp-1)/4

data         = subset(data, select = c(qdate,dlneer))

save(data,file="ea_raw_data_neer.rda")
clear()

##### TERM SPREAD #############
data         = read_excel("ea-term-spread-fred-1.xls", sheet = "Data")
data         = data[-2:-1,]
data         = data[seq(1, nrow(data), 3), ]
data$t10     = as.numeric(data$T10YR) 
tmp          = row(data)
tmp          = tmp[,1]
data$qdate   = 1960 +(tmp-1)/4

data_1        = subset(data, select = c(qdate,t10))

data         = read_excel("ea-term-spread-fred-2.xls", sheet = "Data")
data         = data[-2:-1,]
data         = data[seq(1, nrow(data), 3), ]
data$t3m     = as.numeric(data$T3M) 
tmp          = row(data)
tmp          = tmp[,1]
data$qdate   = 1960 +(tmp-1)/4

data_2       = subset(data, select = c(qdate,t3m))

data         = merge(data_1, data_2,  all=TRUE)

data$term    = data$t10 - data$t3m  

data         = subset(data, select = c(qdate,term))

save(data,file="ea_raw_data_termspread.rda")
clear()

##### UNEMPlOYMENT RATE DATA #############
data  = read_excel("ea-u-rate-fred.xls", sheet = "Data")
data         = data[-2:-1,]
data         = data[seq(1, nrow(data), 3), ]
data$urate   = as.numeric(data$UNRATE)
tmp          = row(data)
tmp          = tmp[,1]
data$qdate   = 1990.50 +(tmp-1)/4

data         = subset(data, select = c(qdate,urate))

save(data,file="ea_raw_data_unemployment.rda")
clear()

##### OIL PRICE DATA #############
data  = read_excel("ea-brent-fred.xls", sheet = "Data")
data$oil     = as.numeric(data$Brent) 
tmp          = row(data)
tmp          = tmp[,1]
data$qdate   = 1987.50 +(tmp-1)/4
data_1       = subset(data, select = c(qdate,oil))

data  = read_excel("ea-usd-fred.xls", sheet = "Data")
data$eur     = as.numeric(data$EUR) 
tmp          = row(data)
tmp          = tmp[,1]
data$qdate   = 1999.00 +(tmp-1)/4
data_2       = subset(data, select = c(qdate,eur))

data         = merge(data_1, data_2, all=TRUE)
data$oil_eur = data$oil*data$eur
lagpad <- function(x, k) {
  res <- c(rep(NA, k), x)[1:length(x)]
  return(res)
}
data$dloil   = 100*(log(data$oil_eur)-log(lagpad(data$oil_eur,4)))
data         = subset(data, select = c(qdate,dloil))

save(data,file="ea_raw_data_oil.rda")
clear()

##### STOCKS #############
data  = read.csv("ea-dax-yahoo.csv")
data         = data[-2:-1,]
data         = data[seq(1, nrow(data), 3), ]
data$stocks  = as.numeric(data$Close) 
tmp          = row(data)
tmp          = tmp[,1]
data$qdate   = 1988 +(tmp-1)/4

dev.lm        = lm(log(stocks) ~ qdate, data=data) 
dev.res       = resid(dev.lm)
data$dlstocks = dev.res

data         = subset(data, select = c(qdate,dlstocks))

save(data,file="ea_raw_data_stocks.rda")
clear()


##### IMPORT PRICE DATA #############
data  = read.csv("ea-import-prices-ecb.csv")
data         = data[-2:-1,]
data         = data[seq(1, nrow(data), 3), ]
lagpad <- function(x, k) {
  res <- c(rep(NA, k), x)[1:length(x)]
  return(res)
}

data$imp     = as.numeric(data$import_price) 
data$dlimp   = 100*(log(data$imp)-log(lagpad(data$imp,4)))
tmp          = row(data)
tmp          = tmp[,1]
data$qdate   = 2005+(tmp-1)/4

data         = subset(data, select = c(qdate,dlimp))

save(data,file="ea_raw_data_imp.rda")
clear()


##### MERGE #############
load("ea_raw_data_neer.rda")
data_new    = data
iter        = 0
data_series =  c('ea_raw_data_imp.rda','ea_raw_data_oil.rda','ea_raw_data_unemployment.rda', 'ea_raw_data_stocks.rda','ea_raw_data_termspread.rda')
for ( i in data_series){
  iter     = iter + 1
  load(i)
#  if (iter <= 2 & iter >=4){
#    data   = data[-4:-1,]
#  } 
  data_new = merge(data_new, data, all=TRUE)
}

data = data_new[data_new$qdate>=1990,]
save(data,file="ea_public_signals_hard.rda")
clear()
